<?php

class Database
{
	public $host     = "localhost";
 	public $database = "ci";
 	public $username = "root";
 	public $password = "1234";
	
	public $conId    = 0;
	public $queryId  = 0;
	public $record   = array();
	public $row;
	public $errNo    = 0;
	public $error    = "";

 	public function __construct(){
  		$this->createConnection(); 
	}

	public function createConnection(){ 
		if( 0 == $this->conId ) 
		{	
			$this->conId = mysql_connect( $this->host, $this->username, $this->password );
		}
		if( !$this->conId )
		{
			$this->stopExec( "Trying to connect.... Result: failed" );
		}
		if( !mysql_query( sprintf( "use %s", $this->database ), $this->conId ) )
		{ 
			$this->stopExec( "cannot use database ".$this->database ); 
		}

		if ( !mysql_set_charset("utf8", $this->conId) )
		{
		 	$this->stopExec( "cannot set character UTF8 ".$this->database );
		}

	}

	public function query($a_queryString){
		$this->queryId = mysql_query( $a_queryString, $this->conId ); 
        $this->row = 0; 
        $this->errNo = mysql_errno(); 
        $this->error = mysql_error(); 
        
        if(!$this->queryId ) 
  			 $this->stopExec( "Invalid SQL String: ".$a_queryString ); 
  		
  		return $this->queryId;
	}

	public function fetchRecord($type = 1){ 
		//1 --> for array
		//2 --> for object
		if($type == 1){
			$record = mysql_fetch_array($this->queryId); //return array
		}
		else
		{
			$record = mysql_fetch_object($this->queryId); //return object
		}
		return $record;
	}
	
	public function stopExec( $msg ) 
	{ 
		printf( "Database error: %s <br>", $msg ); 
		printf( "MySQL Error: %s (%s)<br>", $this->errNo, $this->error ); 
	} 

	
	public function numRows() 
	{ 
		return mysql_num_rows( $this->queryId ); 
	} 	

}

class Quality
{
	public function __construct()
	{
	}
	public function db_get_last3months_data($a_plantname)
	{
		//$a_plantname="invalid";
		$sql = "SELECT * From plant where name='".$a_plantname."' LIMIT 1";		

		$db = new Database();
		$result = $db->query($sql);
		if ( mysql_num_rows($result) == 0 )
			return FALSE;

		$rs = $db->fetchRecord(2);

		$sql = "SELECT * From year where pid=".$rs->pid." ORDER BY year DESC LIMIT 1 ";

		$result = $db->query($sql);
		if ( mysql_num_rows($result) == 0 )
			return FALSE;

		$rs2 = $db->fetchRecord(2);

		// query data for last 3 months based on current month.
		date_default_timezone_set("Asia/Bangkok");
		$thaiyear = intval($rs2->year);
		$year = $thaiyear - 543;
		$date = $year."-".date("m")."-01";
		$startDate = date ('Y-m-d', strtotime('-2 month', strtotime( $date )));

		$sql = "SELECT * From quality inner join year on quality.yid = year.yid where quality.date >= '".$startDate."' and quality.yid=".$rs2->yid." Order by quality.date ASC LIMIT 3";
		
		$result = $db->query($sql);
		if ( mysql_num_rows($result) == 0 )
			return FALSE;		
		
		$result_object = array();
		while ( $row = $db->fetchRecord(2) )
		{
			$result_object[] = $row;
		}
		return $result_object;
	}

	public function db_get_flow_current_year($a_plantname)
	{
		$sql = "SELECT * From plant where name='".$a_plantname."' LIMIT 1";
		$db = new Database();
		$result = $db->query($sql);
		if ( mysql_num_rows($result) == 0 )
			return FALSE;

		$rs = $db->fetchRecord(2);

		$sql = "SELECT * From year where pid=".$rs->pid." ORDER BY year DESC LIMIT 1 ";
		$result = $db->query($sql);
		if ( mysql_num_rows($result) == 0 )
			return FALSE;

		$rs2 = $db->fetchRecord(2);

		$sql = "SELECT date, flow_in, flow_out From flow inner join year on flow.yid = year.yid where flow.yid=".$rs2->yid." Order by flow.date ASC";
		$result = $db->query($sql);
		if ( mysql_num_rows($result) == 0 )
			return FALSE;		
		
		$result_object = array();
		while ( $row = $db->fetchRecord(2) )
		{
			$result_object[] = $row;
		}

		return $result_object;
	}
}


class News
{
	public $m_thaimonth = array("ม.ค.", "ก.พ.", "มี.ค.", "เม.ย", "พ.ค.", "มิ.ย.", "ก.ค.", "ส.ค.", "ก.ย.", "ต.ค.", "พ.ย.", "ธ.ค.");
	
	public function __construct()
	{
		$this->db = new Database();
	}

	// get news to display in index.php
	public function db_get_news()
	{
		$sql = "SELECT * From news ORDER BY date DESC LIMIT 5";
		$result = $this->db->query($sql);
		if ( mysql_num_rows($result) == 0 )
			return FALSE;	
		
		$result_object = array();
		while ( $row = $this->db->fetchRecord(2) )
		{
			$result_object[] = $row;
		}

		return $result_object;
	}

	public function db_get_news_paging($limit, $start)
	{
		$sql = "SELECT * From news ORDER BY date DESC LIMIT ".$start.",".$limit;
		$result = $this->db->query($sql);
		if ( mysql_num_rows($result) == 0 )
			return FALSE;	
		
		$result_object = array();
		while ( $row = $this->db->fetchRecord(2) )
		{
			$result_object[] = $row;
		}

		return $result_object;
	}

	public function db_get_news_by_id($a_id)
	{
		$sql = "SELECT * From news WHERE id=".$a_id." LIMIT 1";
		$result = $this->db->query($sql);
		if ( mysql_num_rows($result) == 0 )
			return FALSE;

		$result_object = $this->db->fetchRecord(2);
		return $result_object;
	}

	public function db_get_count_allnews()
	{
		$sql = "SELECT COUNT(*) FROM news";
		$result = $this->db->query($sql);
		return mysql_result($result, 0);
	}

	public function db_get_thaidate($a_date)
	{
		date_default_timezone_set("Asia/Bangkok");
		$time = strtotime($a_date);
        $day = date("d", $time);
        $month = $this->m_thaimonth[ date("n", $time) ];
        $year = date("Y", $time) + 543;
		return $day." ".$month." ".$year;
	}
}
?>